Excel BI - Excel Challenge 664

excel-challenges
excel-formulas
🔰 After 3 rounds of quality testing, list the remaining batches from Round0 after each Round.
Published

March 24, 2026

Illustration for Excel BI - Excel Challenge 664

Challenge Description

🔰 After 3 rounds of quality testing, list the remaining batches from Round0 after each Round.

Solutions

library(tidyverse)
library(readxl)

path = "Excel/664 Remove Rejected Batches.xlsx"
input = read_excel(path, range = "A3:D16")
test  = read_excel(path, range = "F2:H12")

r1 = setdiff(input$Accept, input$Reject...2)
r2 = setdiff(input$Accept, c(input$Reject...2, input$Reject...3))
r3 = setdiff(input$Accept, c(input$Reject...2, input$Reject...3, input$Reject...4))

longest = max(length(r1), length(r2), length(r3))

result = data.frame(Round1 = c(r1, rep(NA, longest - length(r1))),
                    Round2 = c(r2, rep(NA, longest - length(r2))),
                    Round3 = c(r3, rep(NA, longest - length(r3))))

all.equal(result, test, check.attributes = FALSE)
#> [1] TRUE
  • Logic: Read the workbook ranges needed for the challenge.
  • Strengths: The code maps the workbook rule into a compact, reproducible pipeline.
  • Areas for Improvement: The solution assumes the workbook layout and selected ranges remain stable, so any structural change in the sheet would require small adjustments.
  • Gem: The elegant part is how little code is needed once the correct intermediate representation is chosen.
import pandas as pd

path = "664 Remove Rejected Batches.xlsx"
input = pd.read_excel(path, usecols="A:D", skiprows=2, nrows=14)
test = pd.read_excel(path, usecols="F:H", skiprows=1, nrows=10).rename(columns=lambda x: x.split('.')[0]).apply(lambda x: x.sort_values().values)

input_list_of_lists = input.values.T.tolist()
input_list_of_lists = [[item for item in sublist if pd.notna(item)] for sublist in input_list_of_lists]
result = pd.DataFrame({"Round1": list(set(input_list_of_lists[0]) - set(input_list_of_lists[1]))})
result["Round2"] = pd.Series(list(set(input_list_of_lists[0]) - set(input_list_of_lists[1] + input_list_of_lists[2])))
result["Round3"] = pd.Series(list(set(input_list_of_lists[0]) - set(input_list_of_lists[1] + input_list_of_lists[2] + input_list_of_lists[3])))

for col in ["Round1", "Round2", "Round3"]:
    result[col] = result[col].sort_values().reset_index(drop=True)

print(result.equals(test)) # True

The Python version keeps the algorithm explicit, which helps when the challenge depends on a greedy or iterative rule.

Difficulty Level

Easy / Medium

The business rule is clear, though the workbook still needs a few transformation steps to reach the expected output.